package cn.cdutetc.util;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;


public class DBUtil {
    private static String url=null;
    private static String userName=null;
    private static String pwd=null;
    static
    {
        try {
            FileInputStream in=new FileInputStream(new File("jdbc.properties"));
            Properties p=new Properties();
            p.load(in);
            url=p.getProperty("url");
            userName=p.getProperty("username");
            pwd=p.getProperty("password");
            Class.forName(p.getProperty("drivername"));
        } catch (ClassNotFoundException | IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static Connection getConnection()
    {
        Connection conn=null;
        try {
            conn=DriverManager.getConnection(url,userName,pwd);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }

    public static void createDataBase(String sql)
    {
        Connection conn=getConnection();
        if(conn!=null)
        {
            java.sql.Statement st;
            try {
                st = conn.createStatement();
                st.execute(sql);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    public static void createDataTable(String databaseName,String sql)
    {
        Connection conn=getConnection();
        String sql1="use "+databaseName;
        if(conn!=null)
        {
            java.sql.Statement st;
            try {
                st = conn.createStatement();
                st.execute(sql1);
                st.execute(sql);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    public static void insertData()
    {
        Connection conn=getConnection();
        String sql="insert into user(username,password) values(?,?)";
        if(conn!=null)
        {
            try {
                PreparedStatement ps=conn.prepareStatement(sql);
                ps.setString(1,"jack");
                ps.setString(2, "1234");
                ps.execute();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }
    }
    public static void updateData()
    {

    }
    public static void deleteData()
    {

    }

    //对数据表的增、删、改
    //String sql="delete  from user where id=? and name=? and pwd=?";
    public static int updateData(String sql,Object...objects)
    {
        Connection conn=getConnection();
        if(conn!=null)
        {
            try {
                PreparedStatement ps=conn.prepareStatement(sql);
                for(int i=1;i<=objects.length;i++)
                {
                    ps.setObject(i,objects[i-1]);
                }
                return ps.executeUpdate();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }
        return 0;
    }
//	String sql="select * from user where id=? and name=? and pwd=?";
//	String sql1="select * from student;
//	String sql2="select * from teacher where name like ?";

    //反射
//List<User> listUser=query(User.class,sql,"1","jack","123);  user表假定有id,name,pwd。那么对应的User这个类就一定要有id,name,pwd。而且还必须一摸一样。
//  List<Student> sList=query(Student.class,sql1);
//	List<Teacher> tList=query(Teacher.class,sql2,"t");
    public <T> List<T> query(Class<T> clazz, String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<T> list = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);//可变形参可看作数组
            }
            rs = ps.executeQuery();
            list = new ArrayList<>();
            while (rs.next()) {//next()作用：判断结果集的下一条是否有数据，则返回true并下移指针，否则返回false指针不动
                ResultSetMetaData rsmd = ps.getMetaData();//获取结果集的元数据
                int columnCount = rsmd.getColumnCount();//获取列数
                T t = clazz.newInstance();//通过反射常见对象
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);//获取列的别名
                    //通过反射给columnLable列赋值为columnValue
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }
        return null;
    }
}
